Kerry Osborne's Oracle Blog

Oracle uses the BITAND function heavily (over 1000 times in the catalog.sql script alone as of 10.2.0.4). It’s used to determine whether a particular bit is set or not.

Here’s a link to 11gR1 documentation on the BITAND function. The function takes 2 arguments and performs these basic steps:

1. Converts the 2 arguements to binary (n-bit two’s complement binary integer value)
2. Performs a standard bitwise AND operation on the two strings
3. Converts the binary result back to decimal

So what’s a standard bitwise AND operation actually do?

Well it basically does a logical AND of two bit strings. If the values in any position are both 1’s, then the result will have a 1 in that position, otherwise the result will have a 0 in that position. Here’s a link to the Wikipedia entry on bitwise operations in case you want more information.

Here’s an example:

   
    0101
AND 1001
  = 0001

So what’s it good for and why does Oracle use it so much? Well it turns out to be a very efficient way of checking if a particular bit has been set. This makes use of a technique called “bit masking”. The basic idea is to do a bitwise AND between a bit string of interest and a bit string that is constructed with 0’s in every position except the one of interest. If for example you want to know if the 4th bit is set (i.e. = 1), then you could do a bitwise AND with a string like 1000.

Here’s an example:

    1010101
AND 0001000
  =  0000000

    1011101
AND 0001000
  =  0001000

Oracle’s BITAND function deals with decimal values so it looks a little weird but provides the same functionality. So if you’re checking the first bit it’s 1, the second it’s 2, the 3rd it’s 4, the 4th it’s 8, and so on.

Here’s an example:

> !sql
sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Nov 15 16:23:47 2008

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @fixed_view_def
SQL> select * from v$fixed_view_definition
  2  where view_name like upper('&view_name')
  3  /
Enter value for view_name: GV$PARAMETER

VIEW_NAME
------------------------------
VIEW_DEFINITION
-----------------------------------------------------------------------------------------------------------------------------------------------------------
GV$PARAMETER
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,  decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),  decode(bitand(ksppiflg/65536,3)
,1,'IMMEDIATE',2,'DEFERRED',                                  3,'IMMEDIATE','FALSE'),  decode(bitand(ksppiflg,4),4,'FALSE',
     decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),     decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),  decode(bitand(ksppstvf,2),
2,'TRUE','FALSE'),  decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),  decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'),  ksppdesc, ksppst
cmnt, ksppihash  from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and  ((translate(ksppinm,'_','#') not like '##%') and    ((translate(ksppinm,'_','#') n
ot like '#%')      or (ksppstdf = 'FALSE') or      (bitand(ksppstvf,5) > 0)))

SQL> -- Here it is cleaned it up a bit
SQL>
SQL> !cat junk.dat
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,  
       decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),  
       decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE'),  
       decode(bitand(ksppiflg,4),4,'FALSE',decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),     
       decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),  
       decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),  
       decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),  
       decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'),  ksppdesc, ksppstcmnt, ksppihash  
from x$ksppi x, x$ksppcv y 
where (x.indx = y.indx) 
  and  ((translate(ksppinm,'_','#') not like '##%') 
  and  ((translate(ksppinm,'_','#') not like '#%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5) > 0)))

This view definition has a number of BITAND function calls.

Let’s review a couple of them:

Oracle has used this technique for years in the data dictionary. It’s efficient and frugal with storage. (seems like my Assembly Language class said this could be done with one instruction – but that was back in the dark ages). Anyway, it is used a lot by Oracle. There still isn’t a built-in conversion function for decimal to binary that I am aware of, but Tom Kyte published a pl/sql scripts to do just that many years ago. You can still find a link to it (and others) on this AskTom thread. His num_to_bin function works great.

SQL> set echo on
SQL> @num_to_bin
SQL> create or replace
  2  FUNCTION num_to_bin( i_num IN PLS_INTEGER )
  3  RETURN VARCHAR2 IS
  4     l_Num      PLS_INTEGER;
  5     l_bit      PLS_INTEGER;
  6     l_binary   VARCHAR2(128);
  7  BEGIN
  8     --
  9     l_num := i_num;
 10     --
 11     WHILE l_num > 1 LOOP
 12        l_bit := MOD(l_num,2);
 13        l_binary := TO_CHAR(l_bit)||l_binary;
 14        l_num := FLOOR(l_num / 2);
 15     END LOOP;
 16     --
 17     IF l_num = 1 THEN
 18         l_binary := '1'||l_binary;
 19     END IF;
 20     --
 21     RETURN l_binary;
 22     --
 23  END num_to_bin;
 24  /

Function created.

SQL> select num_to_bin(11) from dual;

NUM_TO_BIN(11)
--------------------------------------------------------------------------------------------
1011


SQL> select num_to_bin(7) from dual;

NUM_TO_BIN(7)
--------------------------------------------------------------------------------------------
111

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options